﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;

namespace HenIT.DatabaseTools
{
    class Program
    {
        static void Main(string[] args)
        {
            string server = "";
            string database = "";
            string outputFileBase = "";
            string prefixFileName = "";

            if (args.Length > 0 && !CommandLineUtils.IsCommand(args, "/?", "-?", "/h", "-h"))
            {
                server = CommandLineUtils.GetCommand(args, "", "/s:", "-s:");
                database = CommandLineUtils.GetCommand(args, "", "/d:", "-d:");
                outputFileBase = CommandLineUtils.GetCommand(args, System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "/od:", "-od:");
                prefixFileName = CommandLineUtils.GetCommand(args, "", "/p:", "-p:");
            }
            else
            {
                ShowHelp();
                return;
            }
            ScriptDBRoles(System.IO.Path.Combine(outputFileBase, prefixFileName + "Roles.sql"), server, database);
            ScriptTables(System.IO.Path.Combine(outputFileBase, prefixFileName + "Tables.sql"), server, database);
            ScriptViews(System.IO.Path.Combine(outputFileBase, prefixFileName + "Views.sql"), server, database);
            ScriptStoredProcs(System.IO.Path.Combine(outputFileBase, prefixFileName + "StoredProcs.sql"), server, database);
#if DEBUG
            Console.WriteLine("Press any key to continue");
            Console.ReadKey();
#endif
        }

        private static void ShowHelp()
        {
            Console.WriteLine("Usage: GenerateDBScripts.exe <-s:server> <-d:database> [-od:outputDirectory] [-p:prefixFileName]");
        }

        static void ScriptTables(string outputFile, string instanceName, string database)
        {
            Console.Write("Generating '" + outputFile + "'...");
            try
            {
                Server srv = new Server(instanceName);
                Database db = srv.Databases[database];
                Scripter scrp = new Scripter(srv);
                scrp.PrefetchObjects = true; // some sources suggest this may speed things up
                scrp.Options.Permissions = true;
                scrp.Options.IncludeHeaders = true;
                scrp.Options.DriAll = true;
                scrp.Options.FileName = outputFile;
                scrp.Options.AppendToFile = false;
                scrp.Options.ToFileOnly = true;
                scrp.Options.ClusteredIndexes = true;
                scrp.Options.Indexes = true;
                scrp.Options.ScriptSchema = true;
                scrp.Options.ScriptDrops = false;
                scrp.Options.Triggers = true;
                scrp.Options.WithDependencies = false;

                var urns = new List<Urn>();
                foreach (Table tb in db.Tables)
                {
                    // check if the table is not a system table
                    if (!tb.IsSystemObject)
                    {
                        urns.Add(tb.Urn);
                        foreach (Index ind in tb.Indexes)
                        {
                            urns.Add(ind.Urn);
                        }
                        foreach (ForeignKey fk in tb.ForeignKeys)
                        {
                            urns.Add(fk.Urn);
                        }
                        foreach (Column col in tb.Columns)
                        {
                            if (col.DefaultConstraint != null)
                                urns.Add(col.DefaultConstraint.Urn);
                        }
                    }
                }
                scrp.Script(urns.ToArray());
                Console.Write(" done");
            }
            catch (Exception ex)
            {
                Console.Write("\r\n" + ex.Message);
            }
            Console.WriteLine();
        }
        static void ScriptViews(string outputFile, string instanceName, string database)
        {
            Console.Write("Generating '" + outputFile + "'...");
            try
            {
                Server srv = new Server(instanceName);
                Database db = srv.Databases[database];
                Scripter scrp = new Scripter(srv);
                scrp.PrefetchObjects = true; // some sources suggest this may speed things up
                scrp.Options.Permissions = false;
                scrp.Options.IncludeHeaders = true;
                scrp.Options.DriAll = true;
                scrp.Options.FileName = outputFile;
                scrp.Options.AppendToFile = false;
                scrp.Options.ToFileOnly = true;
                scrp.Options.ScriptSchema = true;
                scrp.Options.ScriptDrops = true;
                scrp.Options.IncludeIfNotExists = true;
                scrp.Options.WithDependencies = false;

                var urns = new List<Urn>();
                foreach (View view in db.Views)
                {
                    // check if the view is not a system object
                    if (view.IsSystemObject == false)
                    {
                        urns.Add(view.Urn);
                    }
                }
                scrp.Script(urns.ToArray());

                scrp.Options.AppendToFile = true;
                scrp.Options.ScriptDrops = false;
                scrp.Options.Permissions = true;
                scrp.Script(urns.ToArray());
                Console.Write(" done");
            }
            catch (Exception ex)
            {
                Console.Write("\r\n" + ex.Message);
            }
            Console.WriteLine();
        }
        static void ScriptStoredProcs(string outputFile, string instanceName, string database)
        {
            Console.Write("Generating '" + outputFile + "'...");
            try
            {
                Server srv = new Server(instanceName);
                Database db = srv.Databases[database];
                Scripter scrp = new Scripter(srv);
                scrp.PrefetchObjects = true; // some sources suggest this may speed things up
                scrp.Options.Permissions = false;
                scrp.Options.IncludeHeaders = true;
                scrp.Options.DriAll = true;
                scrp.Options.FileName = outputFile;
                scrp.Options.AppendToFile = false;
                scrp.Options.ToFileOnly = true;
                scrp.Options.ScriptSchema = true;
                scrp.Options.ScriptDrops = true;
                scrp.Options.IncludeIfNotExists = true;
                scrp.Options.WithDependencies = false;

                var urns = new List<Urn>();
                foreach (StoredProcedure sp in db.StoredProcedures)
                {
                    // check if the procedure is not a system object
                    if (!sp.IsSystemObject)
                    {
                        urns.Add(sp.Urn);
                    }
                }
                foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
                {
                    if (!udf.IsSystemObject)
                    {
                        urns.Add(udf.Urn);
                    }
                }
                scrp.Script(urns.ToArray());

                scrp.Options.AppendToFile = true;
                scrp.Options.ScriptDrops = false;
                scrp.Options.Permissions = true;
                scrp.Script(urns.ToArray());
                Console.Write(" done");
            }
            catch (Exception ex)
            {
                Console.Write("\r\n" + ex.Message);
            }
            Console.WriteLine();
        }
        static void ScriptDBRoles(string outputFile, string instanceName, string database)
        {
            Console.Write("Generating '" + outputFile + "'...");
            try
            {
                Server srv = new Server(instanceName);
                Database db = srv.Databases[database];
                Scripter scrp = new Scripter(srv);
                scrp.Options.FileName = outputFile;
                scrp.PrefetchObjects = true; // some sources suggest this may speed things up
                scrp.Options.IncludeDatabaseRoleMemberships = true;
                scrp.Options.ScriptDrops = false;
                var urns = new List<Urn>();
                foreach (DatabaseRole rl in db.Roles)
                {
                    // check if the procedure is not a system object
                    if (!rl.IsFixedRole)
                    {
                        urns.Add(rl.Urn);
                    }
                }
                foreach (ApplicationRole rl in db.ApplicationRoles)
                {
                    // check if the procedure is not a system object
                    
                    {
                        urns.Add(rl.Urn);
                    }
                }
                scrp.Script(urns.ToArray());
                Console.Write(" done");
            }
            catch (Exception ex)
            {
                Console.Write("\r\n" + ex.Message);
            }
            Console.WriteLine();
        }
    }
}
